package com.mareas.web.dao.impl;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.mareas.web.dao.Dao;
import com.mareas.web.model.Operacion;
import com.mareas.web.model.integration.InformeClienteDTO;

@Repository
public class DaoImpl extends HibernateDaoSupport implements Dao {
	@Autowired
	public DaoImpl(SessionFactory sessionFactory) {
		super.setSessionFactory(sessionFactory);
	}

	@Transactional
	public void persist(Object entity) {
		getHibernateTemplate().saveOrUpdate(entity);
	}

	@Transactional
	public void update(Object entity) {
		getHibernateTemplate().merge(entity);
	}
	
	@Transactional
	public void remove(Object entity) {
		getHibernateTemplate().delete(entity);
	}

	@Transactional
	public void persist(Object[] entities) {
		for (int i = 0; i < entities.length; i++) {
			persist(entities[i]);
		}
	}

	@Transactional(readOnly = true)
	public <T> List<T> find(Class<T> entityClass) {
		final List<T> entities = getHibernateTemplate().loadAll(entityClass);
		return entities;
	}

	@Transactional(readOnly = true)
	public <T> T load(Class<T> entityClass, Serializable id) {
		final T entity = (T) getHibernateTemplate().load(entityClass, id);
		return entity;
	}

	@Transactional(readOnly = true)
	public <T> List<T> find(String hql) {
		final List<T> entities = getHibernateTemplate().find(hql);
		return entities;
	}

	@Transactional
	public List<InformeClienteDTO> obtenerListadoInformeCliente() {
		try {
			List<InformeClienteDTO> resultList = new ArrayList<InformeClienteDTO>();

			HibernateCallback<List<InformeClienteDTO>> callback = new HibernateCallback<List<InformeClienteDTO>>() {
				public List<InformeClienteDTO> doInHibernate(Session session)
						throws HibernateException, SQLException {

					Query query = session
							.createQuery("SELECT new com.mareas.web.model.integration."
									+ "InformeClienteDTO(Op.cliente.clienteId,Op.cliente.clienteNombre,count(*),sum(Op.operacionCostoTotal)) "
									+ "from Operacion Op "
									+ "group by Op.cliente.clienteId");
					return (List<InformeClienteDTO>) query.list();
				}
			};

			resultList = (List<InformeClienteDTO>) this.getHibernateTemplate()
					.execute(callback);
			return resultList;
		} catch (HibernateException hex) {
			throw hex;
		}
	}
	
	@Transactional
	public List<InformeClienteDTO> obtenerListadoInformeClientePeriodo(final Integer clienteIdParam) {
		try {
			List<InformeClienteDTO> resultList = new ArrayList<InformeClienteDTO>();

			HibernateCallback<List<InformeClienteDTO>> callback = new HibernateCallback<List<InformeClienteDTO>>() {
				public List<InformeClienteDTO> doInHibernate(Session session)
						throws HibernateException, SQLException {

					Query query = session
							.createQuery("SELECT new com.mareas.web.model.integration."
									+ "InformeClienteDTO(Op.cliente.clienteId,Op.cliente.clienteNombre,month(Op.operacionFecha),year(Op.operacionFecha),count(*)) "
									+ "from Operacion Op "
									+ "where Op.cliente.clienteId=:clienteIdParam "
									+ "group by Op.cliente.clienteId,year(Op.operacionFecha),month(Op.operacionFecha)")									
									.setInteger("clienteIdParam", clienteIdParam);
					return (List<InformeClienteDTO>) query.list();
				}
			};

			resultList = (List<InformeClienteDTO>) this.getHibernateTemplate()
					.execute(callback);
			return resultList;
		} catch (HibernateException hex) {
			throw hex;
		}
	}
}
